package Model;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Database.*;
import Helper.*;

public class ProductM extends Model {
    private ArrayList<Product> cache = null;

    public boolean edit(Product p){
        ProductDB db = new ProductDB();

        return db.update(p);
    }

    public boolean remove(int id){
        ProductDB db = new ProductDB();

        return db.delete(id);
    }

    public boolean add(Product p){
        ProductDB db = new ProductDB();

        return db.insert(p);
    }

    public ArrayList<Product> all(){
        init();

        return cache;
    }

    public ArrayList<Product> find(int id){
        init();

        ArrayList<Product> matches = new ArrayList<>();

        if (cache != null) {
            for( Product item : cache ){
                if( item.id == id ){
                    matches.add(item);
                }
            }
        } else {
            System.err.println("未找到结果！");
        }


        return matches;
    }

    public ArrayList<Product> find(String name){
        // override

        ProductDB db = new ProductDB();

        CachedRowSetImpl res = db.select(name);

        return createproductArr(res);
    }

    protected void init(){
        // override

        if( cache == null ){
            ProductDB db = new ProductDB();

            CachedRowSetImpl res = db.select();

            cache = createproductArr(res);
        }
    }

    private ArrayList<Product> createproductArr(CachedRowSetImpl res){
        // override

        ArrayList<Product> result = new ArrayList<>();

        try {
            while (res.next()) {
                Product p = new Product();
                p.id = res.getInt(1);
                p.name = res.getString(2);
                p.price = res.getFloat(3);
                p.amount = res.getInt(4);
                p.vendor = res.getString(5);
                p.status = res.getInt(6);

                result.add(p);
            }
        } catch (SQLException exception) {
            System.err.println("数据库出错！");
            if(Helper.isDev){
                exception.printStackTrace();
            }
        }

        return result;
    }
}

class ProductDB extends DB{
    CachedRowSetImpl select() {
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            Statement statement = dbConnection.createStatement();
            String sql = "SELECT p.id,p.name,p.price,p.amount,a.name,p.status FROM tb_product p,tb_account a WHERE p.vendor_id=a.id";
            ResultSet res = statement.executeQuery(sql);

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
            // cache the result set.
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    CachedRowSetImpl select(String name) {
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            String sql = "SELECT p.id,p.name,p.price,p.amount,a.name,p.status FROM tb_product p,tb_account a WHERE p.vendor_id=a.id AND p.name LIKE ?";

            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setString(1, "%"+name+"%");

            ResultSet res = statement.executeQuery();

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    boolean insert(Product p){
        int result = -1;

        try{
            connect();

            String sql = "INSERT INTO tb_product(name,price,amount,vendor_id,status) VALUES(?,?,?,?,?)";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setString(1,p.name);
            statement.setFloat(2,p.price);
            statement.setInt(3,p.amount);
            statement.setInt(4, Integer.parseInt(Helper.getSession(1)));
            statement.setInt(5, p.status);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    boolean delete(int id){
        return super.delete("tb_product", id);
    }

    boolean update(Product p){
        int result = -1;

        try{
            connect();

            String sql = "UPDATE tb_product SET name=?,price=?,amount=?,status=? WHERE id=?";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setString(1, p.name);
            statement.setFloat(2, p.price);
            statement.setInt(3, p.amount);
            statement.setInt(4, p.status);
            statement.setInt(5, p.id);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }
}
